
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/01/2013 00:06:10
-- Generated from EDMX file: C:\Users\etien_000\Documents\Cours\IAATO\DAL\DAL\Model.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [BaseIAATO];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ZONE_SITE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SITEJeu] DROP CONSTRAINT [FK_ZONE_SITE];
GO
IF OBJECT_ID(N'[dbo].[FK_COMPAGNIE_NAVIRE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[NAVIREJeu] DROP CONSTRAINT [FK_COMPAGNIE_NAVIRE];
GO
IF OBJECT_ID(N'[dbo].[FK_PROGRAMME_CRENEAU]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CRENEAUJeu] DROP CONSTRAINT [FK_PROGRAMME_CRENEAU];
GO
IF OBJECT_ID(N'[dbo].[FK_NAVIRE_PROGRAMME]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[PROGRAMMEJeu] DROP CONSTRAINT [FK_NAVIRE_PROGRAMME];
GO
IF OBJECT_ID(N'[dbo].[FK_ACTIVITE_SITE_LINKACTIVITE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ACTIVITE_SITE_LINKJeu] DROP CONSTRAINT [FK_ACTIVITE_SITE_LINKACTIVITE];
GO
IF OBJECT_ID(N'[dbo].[FK_ACTIVITE_SITE_LINKSITE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ACTIVITE_SITE_LINKJeu] DROP CONSTRAINT [FK_ACTIVITE_SITE_LINKSITE];
GO
IF OBJECT_ID(N'[dbo].[FK_CRENEAU_ACTIVITE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CRENEAUJeu] DROP CONSTRAINT [FK_CRENEAU_ACTIVITE];
GO
IF OBJECT_ID(N'[dbo].[FK_CRENEAUSITE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CRENEAUJeu] DROP CONSTRAINT [FK_CRENEAUSITE];
GO
IF OBJECT_ID(N'[dbo].[FK_NAVIRE_CATEGORIE]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[NAVIREJeu] DROP CONSTRAINT [FK_NAVIRE_CATEGORIE];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[NAVIREJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[NAVIREJeu];
GO
IF OBJECT_ID(N'[dbo].[SITEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SITEJeu];
GO
IF OBJECT_ID(N'[dbo].[ACTIVITEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ACTIVITEJeu];
GO
IF OBJECT_ID(N'[dbo].[COMPAGNIEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[COMPAGNIEJeu];
GO
IF OBJECT_ID(N'[dbo].[ZONEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ZONEJeu];
GO
IF OBJECT_ID(N'[dbo].[PROGRAMMEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[PROGRAMMEJeu];
GO
IF OBJECT_ID(N'[dbo].[CRENEAUJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CRENEAUJeu];
GO
IF OBJECT_ID(N'[dbo].[ACTIVITE_SITE_LINKJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ACTIVITE_SITE_LINKJeu];
GO
IF OBJECT_ID(N'[dbo].[CATEGORIEJeu]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CATEGORIEJeu];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'NAVIREJeu'
CREATE TABLE [dbo].[NAVIREJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NOM] nvarchar(max)  NOT NULL,
    [MAX_PASSAGERS] int  NOT NULL,
    [ADRESSE_EMAIL] nvarchar(max)  NOT NULL,
    [NUM_TEL] nvarchar(max)  NOT NULL,
    [COMPAGNIEID] int  NOT NULL,
    [IMMATRICULATION] nvarchar(max)  NOT NULL,
    [CATEGORIEID] int  NOT NULL
);
GO

-- Creating table 'SITEJeu'
CREATE TABLE [dbo].[SITEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [ZONEID] int  NOT NULL,
    [NOM] nvarchar(max)  NOT NULL,
    [LATITUDE] float  NOT NULL,
    [LONGITUDE] float  NOT NULL
);
GO

-- Creating table 'ACTIVITEJeu'
CREATE TABLE [dbo].[ACTIVITEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NOM] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'COMPAGNIEJeu'
CREATE TABLE [dbo].[COMPAGNIEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NOM] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ZONEJeu'
CREATE TABLE [dbo].[ZONEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NOM] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'PROGRAMMEJeu'
CREATE TABLE [dbo].[PROGRAMMEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NAVIREID] int  NOT NULL,
    [DATE] datetime  NOT NULL
);
GO

-- Creating table 'CRENEAUJeu'
CREATE TABLE [dbo].[CRENEAUJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [PROGRAMMEID] int  NOT NULL,
    [ORDRE] int  NOT NULL,
    [ACTIVITEID] int  NULL,
    [SITEID] int  NOT NULL
);
GO

-- Creating table 'ACTIVITE_SITE_LINKJeu'
CREATE TABLE [dbo].[ACTIVITE_SITE_LINKJeu] (
    [ACTIVITEID] int  NOT NULL,
    [SITEID] int  NOT NULL
);
GO

-- Creating table 'CATEGORIEJeu'
CREATE TABLE [dbo].[CATEGORIEJeu] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [NOM] nvarchar(max)  NOT NULL,
    [CAPACITE] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'NAVIREJeu'
ALTER TABLE [dbo].[NAVIREJeu]
ADD CONSTRAINT [PK_NAVIREJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'SITEJeu'
ALTER TABLE [dbo].[SITEJeu]
ADD CONSTRAINT [PK_SITEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ACTIVITEJeu'
ALTER TABLE [dbo].[ACTIVITEJeu]
ADD CONSTRAINT [PK_ACTIVITEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'COMPAGNIEJeu'
ALTER TABLE [dbo].[COMPAGNIEJeu]
ADD CONSTRAINT [PK_COMPAGNIEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ZONEJeu'
ALTER TABLE [dbo].[ZONEJeu]
ADD CONSTRAINT [PK_ZONEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'PROGRAMMEJeu'
ALTER TABLE [dbo].[PROGRAMMEJeu]
ADD CONSTRAINT [PK_PROGRAMMEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CRENEAUJeu'
ALTER TABLE [dbo].[CRENEAUJeu]
ADD CONSTRAINT [PK_CRENEAUJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ACTIVITEID], [SITEID] in table 'ACTIVITE_SITE_LINKJeu'
ALTER TABLE [dbo].[ACTIVITE_SITE_LINKJeu]
ADD CONSTRAINT [PK_ACTIVITE_SITE_LINKJeu]
    PRIMARY KEY NONCLUSTERED ([ACTIVITEID], [SITEID] ASC);
GO

-- Creating primary key on [ID] in table 'CATEGORIEJeu'
ALTER TABLE [dbo].[CATEGORIEJeu]
ADD CONSTRAINT [PK_CATEGORIEJeu]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ZONEID] in table 'SITEJeu'
ALTER TABLE [dbo].[SITEJeu]
ADD CONSTRAINT [FK_ZONE_SITE]
    FOREIGN KEY ([ZONEID])
    REFERENCES [dbo].[ZONEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ZONE_SITE'
CREATE INDEX [IX_FK_ZONE_SITE]
ON [dbo].[SITEJeu]
    ([ZONEID]);
GO

-- Creating foreign key on [COMPAGNIEID] in table 'NAVIREJeu'
ALTER TABLE [dbo].[NAVIREJeu]
ADD CONSTRAINT [FK_COMPAGNIE_NAVIRE]
    FOREIGN KEY ([COMPAGNIEID])
    REFERENCES [dbo].[COMPAGNIEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_COMPAGNIE_NAVIRE'
CREATE INDEX [IX_FK_COMPAGNIE_NAVIRE]
ON [dbo].[NAVIREJeu]
    ([COMPAGNIEID]);
GO

-- Creating foreign key on [PROGRAMMEID] in table 'CRENEAUJeu'
ALTER TABLE [dbo].[CRENEAUJeu]
ADD CONSTRAINT [FK_PROGRAMME_CRENEAU]
    FOREIGN KEY ([PROGRAMMEID])
    REFERENCES [dbo].[PROGRAMMEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PROGRAMME_CRENEAU'
CREATE INDEX [IX_FK_PROGRAMME_CRENEAU]
ON [dbo].[CRENEAUJeu]
    ([PROGRAMMEID]);
GO

-- Creating foreign key on [NAVIREID] in table 'PROGRAMMEJeu'
ALTER TABLE [dbo].[PROGRAMMEJeu]
ADD CONSTRAINT [FK_NAVIRE_PROGRAMME]
    FOREIGN KEY ([NAVIREID])
    REFERENCES [dbo].[NAVIREJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NAVIRE_PROGRAMME'
CREATE INDEX [IX_FK_NAVIRE_PROGRAMME]
ON [dbo].[PROGRAMMEJeu]
    ([NAVIREID]);
GO

-- Creating foreign key on [ACTIVITEID] in table 'ACTIVITE_SITE_LINKJeu'
ALTER TABLE [dbo].[ACTIVITE_SITE_LINKJeu]
ADD CONSTRAINT [FK_ACTIVITE_SITE_LINKACTIVITE]
    FOREIGN KEY ([ACTIVITEID])
    REFERENCES [dbo].[ACTIVITEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [SITEID] in table 'ACTIVITE_SITE_LINKJeu'
ALTER TABLE [dbo].[ACTIVITE_SITE_LINKJeu]
ADD CONSTRAINT [FK_ACTIVITE_SITE_LINKSITE]
    FOREIGN KEY ([SITEID])
    REFERENCES [dbo].[SITEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ACTIVITE_SITE_LINKSITE'
CREATE INDEX [IX_FK_ACTIVITE_SITE_LINKSITE]
ON [dbo].[ACTIVITE_SITE_LINKJeu]
    ([SITEID]);
GO

-- Creating foreign key on [ACTIVITEID] in table 'CRENEAUJeu'
ALTER TABLE [dbo].[CRENEAUJeu]
ADD CONSTRAINT [FK_CRENEAU_ACTIVITE]
    FOREIGN KEY ([ACTIVITEID])
    REFERENCES [dbo].[ACTIVITEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CRENEAU_ACTIVITE'
CREATE INDEX [IX_FK_CRENEAU_ACTIVITE]
ON [dbo].[CRENEAUJeu]
    ([ACTIVITEID]);
GO

-- Creating foreign key on [SITEID] in table 'CRENEAUJeu'
ALTER TABLE [dbo].[CRENEAUJeu]
ADD CONSTRAINT [FK_CRENEAUSITE]
    FOREIGN KEY ([SITEID])
    REFERENCES [dbo].[SITEJeu]
        ([ID])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CRENEAUSITE'
CREATE INDEX [IX_FK_CRENEAUSITE]
ON [dbo].[CRENEAUJeu]
    ([SITEID]);
GO

-- Creating foreign key on [CATEGORIEID] in table 'NAVIREJeu'
ALTER TABLE [dbo].[NAVIREJeu]
ADD CONSTRAINT [FK_NAVIRE_CATEGORIE]
    FOREIGN KEY ([CATEGORIEID])
    REFERENCES [dbo].[CATEGORIEJeu]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_NAVIRE_CATEGORIE'
CREATE INDEX [IX_FK_NAVIRE_CATEGORIE]
ON [dbo].[NAVIREJeu]
    ([CATEGORIEID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------